﻿Public Class perOcorrencias
    Inherits perPadrao
#Region "Variáveis"
    Private objBusca As New perProximoId
#End Region

#Region "Métodos"

    Public Function RetornaUltimoContato(ByVal iIDCliente As Int32) As Date
        Dim sSql As String
        Dim dsDados As New DataSet

        Try
            sSql = "SELECT Max(DataInicio) as Data" & vbCrLf
            sSql &= "FROM DC_Ocorrencias " & vbCrLf
            sSql &= "WHERE DC_Ocorrencias.IDCliente = " & iIDCliente

            dsDados = MyBase.executarConsulta(sSql, "DC_Ocorrencias")

            With dsDados.Tables("DC_Ocorrencias")

                If (.Rows.Count > 0) Then
                    Return perConversao.nuloParaData(.Rows(0).Item("Data"))
                Else
                    Return Nothing
                End If

            End With

        Catch ex As Exception

            Throw New Exception("Exceção no método 'RetornaUltimoContato'. " & Environment.NewLine & Environment.NewLine & ex.Message)

        End Try

    End Function

    Public Function insereOcorrencia(ByVal iIDCliente As Int32, _
                                     ByVal iIDContato As Int32, _
                                     ByVal sDescricao As String, _
                                     ByVal iIDUsuario As Int32, _
                                     ByVal dtDataInicio As Date, _
                                     ByVal dtDataFim As Date) As Int32
        Dim sSql As String
        Dim iIDOcorrencia As Int32
        Try
            iIDOcorrencia = objBusca.BuscaID("IDOcorrencia", "DC_Ocorrencias")

            sSql = "INSERT INTO DC_Ocorrencias ( " & vbCrLf
            sSql &= "                            IDOcorrencia, " & vbCrLf
            sSql &= "                            IDCliente, " & vbCrLf
            sSql &= "                            IDContato, " & vbCrLf
            sSql &= "                            IDUsuario, " & vbCrLf
            sSql &= "                            Observacoes, " & vbCrLf
            sSql &= "                            DataInicio, " & vbCrLf
            sSql &= "                            DataFim " & vbCrLf
            sSql &= "                            ) " & vbCrLf
            sSql &= "                     VALUES(" & iIDOcorrencia & ", " & vbCrLf
            sSql &= "                            " & iIDCliente & "," & vbCrLf
            sSql &= "                            " & iIDContato & "," & vbCrLf
            sSql &= "                            " & IIf(iIDUsuario = 0, "null", iIDUsuario) & "," & vbCrLf
            sSql &= "                           '" & sDescricao & "'," & vbCrLf
            sSql &= "                            '" & dtDataInicio & "'," & vbCrLf
            sSql &= "                            '" & dtDataFim & "')" & vbCrLf

            MyBase.executarAcao(sSql)

            Return iIDOcorrencia

        Catch ex As Exception
            Throw New Exception("Exceção no método 'insereOcorrencia'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
    Public Function alteraOcorrencia(ByVal iIDOcorrencia As Int32, ByVal iIDCliente As Int32, _
                                   ByVal iIDContato As Int32, _
                                   ByVal sDescricao As String, _
                                   ByVal iIDUsuario As Int32, _
                                   ByVal dtDataInicio As Date, _
                                   ByVal dtDataFim As Date) As Int32
        Dim sSql As String

        Try

            sSql = "UPDATE DC_Ocorrencias SET " & vbCrLf
            sSql &= "                       IDCliente = " & iIDCliente & ", " & vbCrLf
            sSql &= "                       IDContato = " & iIDContato & ", " & vbCrLf
            sSql &= "                       IDUsuario = " & iIDUsuario & ", " & vbCrLf
            sSql &= "                       Observacoes = '" & sDescricao & "' " & vbCrLf
            sSql &= "WHERE IDOcorrencia = " & iIDOcorrencia

            MyBase.executarAcao(sSql)

            Return iIDOcorrencia

        Catch ex As Exception
            Throw New Exception("Exceção no método 'insereOcorrencia'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function

    Public Function selecionaContatosRealizados(ByVal iIdCliente As Int32, _
                                            ByVal iIdStatus As Int32, _
                                            ByVal iIDUsuario As Int32, _
                                            ByVal dtDataInicio As Date, _
                                            ByVal dtDataFim As Date) As DataTable
        Dim sSql As String
        Dim dsDados As New DataSet

        Try
            sSql = "SELECT DC_Ocorrencias.IDOCorrencia, " & vbCrLf
            sSql &= "   DC_Ocorrencias.DataInicio as 'DataInicio', " & vbCrLf
            sSql &= "   DC_Ocorrencias.DataFim, " & vbCrLf
            sSql &= "   DC_Ocorrencias.IDCliente, " & vbCrLf
            sSql &= "   DC_Ocorrencias.IDContato, " & vbCrLf
            sSql &= "   DC_Ocorrencias.IDUsuario, " & vbCrLf
            sSql &= "   DC_Clientes.RazaoSocial, " & vbCrLf
            sSql &= "   DC_Contatos.NomeContato, " & vbCrLf
            sSql &= "   CASE WHEN DC_Ocorrencias.IDUsuario IS NULL THEN 'MIGRADO' ELSE DC_Usuarios.Nome END AS 'NomeUsuario', " & vbCrLf
            sSql &= "   DC_Ocorrencias.Observacoes, " & vbCrLf
            sSql &= "   DC_Status.Descricao AS 'Status' " & vbCrLf
            sSql &= "FROM " & vbCrLf
            sSql &= "   DC_Ocorrencias " & vbCrLf
            sSql &= "INNER JOIN DC_Clientes ON DC_Ocorrencias.IDCliente = DC_Clientes.IDCliente " & vbCrLf
            sSql &= "LEFT JOIN DC_Contatos ON DC_Ocorrencias.IDContato = DC_Contatos.IDContato " & vbCrLf
            sSql &= "LEFT JOIN DC_Usuarios ON DC_Ocorrencias.IDUsuario = DC_Usuarios.IDUsuario " & vbCrLf
            sSql &= "LEFT JOIN DC_Status ON DC_Clientes.idstatus = DC_Status.IDStatus  " & vbCrLf
            sSql &= "WHERE "
            sSql &= "   DC_Ocorrencias.DataInicio BETWEEN '" & dtDataInicio & "'  AND '" & dtDataFim & "'" & vbCrLf

            If iIdCliente > 0 Then
                sSql &= "AND DC_Clientes.IDCliente = " & iIdCliente & vbCrLf
            End If

            If iIDUsuario > 0 Then
                sSql &= "AND DC_Usuarios.IDUsuario = " & iIDUsuario & vbCrLf
            End If

            If iIdStatus > 0 Then
                sSql &= "AND DC_Status.IDStatus = " & iIdStatus & vbCrLf
            End If
            sSql &= "ORDER BY DC_Ocorrencias.DataInicio desc" & vbCrLf
            dsDados = MyBase.executarConsulta(sSql, "DC_Ocorrencias")

            With dsDados.Tables("DC_Ocorrencias")

                If (.Rows.Count > 0) Then
                    Return dsDados.Tables("DC_Ocorrencias")
                Else
                    Return Nothing
                End If

            End With

        Catch ex As Exception

            Throw New Exception("Exceção no método 'selecionaContatosRealizados'. " & Environment.NewLine & Environment.NewLine & ex.Message)

        End Try
    End Function

    Public Function selecionaOcorrencia(ByVal iIDOcorrencia As Int32) As DataRow
        Dim sSql As String
        Dim dsOcorrencia As DataSet

        Try
            sSql = "SELECT * " & vbCrLf
            sSql &= " FROM DC_Ocorrencias" & vbCrLf
            sSql &= "WHERE IDOcorrencia = " & iIDOcorrencia

            dsOcorrencia = MyBase.executarConsulta(sSql, "DC_Ocorrencias")

            If Not dsOcorrencia Is Nothing AndAlso dsOcorrencia.Tables("DC_Ocorrencias").Rows.Count > 0 Then

                Return dsOcorrencia.Tables("DC_Ocorrencias").Rows(0)

            Else

                Return Nothing

            End If

        Catch ex As Exception
            Throw New Exception("Exceção no método 'selecionaOcorrencia'." & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function

#End Region

End Class
